SQL SEQUENCES

    In SQL a Sequence is defined as a Database object which is generally used to generate an integer sequence. In Database tables, Sequences often used to populate the numeric primary key column. However, this can also be done using the AUTO_INCREMENT constraint, but Sequences give more flexibility, and here we can define the integer sequence in a different order.

    SQL SEQUENCES

    Here are some essential points related to the SQL SEQUENCES:

    • It is a database user-defined object whose main task is to generate an integer sequence on the user demand.
    • Sequences are flexible, and the user can define its order.
    • User can generate the sequence of ascending or descending order. User can also define interval and maximum & minimum values of the series.

    Note: All the RDBMS does not support CREATE SEQUENCE command, for instance, in MYSQL SEQUENCE is created by using Constraint AUTO_INCREAMENT. However, Oracle Database give support for CREATE SEQUENCE command.

    MySQL Sequence 

    CREATE TABLE Studnets (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

    SQL SEQUENCE Syntax(Oracle) 

    CREATE SEQUENCE sequence_name
START WITH start_num
INCREMENT BY interval_value
MAXIVALUE max_lim | NOMAXVALUE
MINVALUE min_limit | NOMINVALUE
CACHE cache_num |NOCACHE
CYCLE | NOCYCLE
ORDER |NOORDER; 

    Syntax Explanation

    • CREATE SEQUENCE: To create a sequence, we need to use the SQL CREATE SEQUENCE statement followed by the sequence_name.
    • START WITH: It is the first attribute of the CREATE SEQUENCE statement which specify the first numeric value of the sequence, which means it represents the initial value from where the series will start.
    • INCREMENT BY: It accepts a numeric value, and the number we specify here will act as the interval between the sequence values.

    Here we can pass any positive or negative value. If the INCREMENT BY value is positive, then the ascending sequence will be generated, if the value is negative, the Descending series will be created. If we do not mention this attribute then by default, the SQL engine will increment the sequence by Interval 1.

    • MAXVALUE: This attribute set the upper bound of the sequence. The value of the MAXVALUE must be higher than MINVALUE and START WITH values.
    • If you do not want to mention any MAXVALUE, then use the NOMAXVALUE keyword.
    • MINVLAUE: It is similar to the MAXVALUE, but it set the minimum boundary of the sequence. If you do not want to mention a MINVALUE, then use the NOMINVALUE keyword.
    • CACHE: It specifies the number of integers that need to be kept in the memory. By default, the SQL Engine keeps only 20 cache values, but it can be modified using the CACH attribute. If you do not mention the CASH value by default, the SQL engine will keep 20 integers in memory. The NOCACHE value specifies that memory should not be pre-allocated to the integers.
    • CYCLE or NOCYLCE: These are two different flags of CREATE SEQUENCE command. If you set the CYCLE flag, then the sequence will continue to generate the integer values once you reach either minimum or maximum value. On the other hand, the NOCYCLE flag stops generating values once the sequence value reaches its maximum or minimum value. By default, SQL follows the NOCYCLE flag.
    • ORDER or NOORDER: ORDER flag makes sure that Sequence values generated according to the order of request. Use NOORDER when you do not want to generate sequence number in order of request.

    Example 

    CREATE SEQUENCE sq
START WITH 1
INCREMENT BY 2
MAXVALUE 20
MINVALUE 1
CACHE 5
CYCLE
ORDER;

    Output 

    Sequence sq created

    Verify and Initialize the Sequence

    Before we use the sequence, we need to initialize it. To initialize a sequence, we can use the NEXTVAL pseudo column. Example 

    SELECT sq.NEXTVAL FROM Students;

    Output 

    1

    Example 

    CREATE TABLE Studnets (
    ID INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

INSERT INTO Students(sq.NEXTVAL, "Sam","Hollow");
INSERT INTO Students(sq.NEXTVAL, "Jolly","Hogland");

    Summary

    • It is a database object which is used to generate a sequence of integers.
    • All the RDBMS does not support CREATE SEQUENCE command.
    • In MYSQL, we have AUTO_INCREAMENT constraint to create a sequence.
    • RDBMS like oracle use CREATE SEQUENCE command.
    • All the attributes and flags of CREATE SEQUENCE are optional. SQL engine has default values for them if the user does not specify any.

    People are also reading:

    Previous Tutorial